﻿@{
    ViewBag.Title = "Paging & Sorting Demo";
}

<div id="StudentTableContainer">
</div>
<script type="text/javascript">

    $(document).ready(function () {

        $('#StudentTableContainer').jtable({
            title: 'Student List',
            paging: true,
            pageSize: 10,
            sorting: true,
            defaultSorting: 'Name ASC',
            actions: {
                listAction: '@Url.Action("StudentList")',
                deleteAction: '@Url.Action("DeleteStudent")',
                updateAction: '@Url.Action("UpdateStudent")',
                createAction: '@Url.Action("CreateStudent")'
            },
            fields: {
                StudentId: {
                    key: true,
                    create: false,
                    edit: false,
                    list: false
                },
                Name: {
                    title: 'Name',
                    width: '23%'
                },
                EmailAddress: {
                    title: 'Email address',
                    list: false
                },
                Password: {
                    title: 'User Password',
                    type: 'password',
                    list: false
                },
                Gender: {
                    title: 'Gender',
                    width: '13%',
                    options: { 'M': 'Male', 'F': 'Female' }
                },
                CityId: {
                    title: 'City',
                    width: '12%',
                    options: '@Url.Action("GetCityOptions")'
                },
                BirthDate: {
                    title: 'Birth date',
                    width: '15%',
                    type: 'date',
                    displayFormat: 'yy-mm-dd'
                },
                Education: {
                    title: 'Education',
                    list: false,
                    type: 'radiobutton',
                    options: { '1': 'Primary school', '2': 'High school', '3': 'University' }
                },
                About: {
                    title: 'About this person',
                    type: 'textarea',
                    list: false
                },
                IsActive: {
                    title: 'Status',
                    width: '12%',
                    type: 'checkbox',
                    values: { 'false': 'Passive', 'true': 'Active' },
                    defaultValue: 'true'
                },
                RecordDate: {
                    title: 'Record date',
                    width: '15%',
                    type: 'date',
                    displayFormat: 'dd.mm.yy',
                    create: false,
                    edit: false,
                    sorting: false //This column is not sortable!
                }
            }
        });

        //Load student list from server
        $('#StudentTableContainer').jtable('load');
    });

</script>
<br />
<hr />
<h3>
    HTML code</h3>
<pre class="brush:html">&lt;div id=&quot;StudentTableContainer&quot;&gt;&lt;/div&gt;</pre>
<h3>
    Javascript code</h3>
<pre class="brush:js; highlight: [7,8,9,10]">&lt;script type=&quot;text/javascript&quot;&gt;

    $(document).ready(function () {

        $(&#39;#StudentTableContainer&#39;).jtable({
            title: &#39;The Student List&#39;,
            paging: true, //Enable paging
            pageSize: 10, //Set page size (default: 10)
            sorting: true, //Enable sorting
            defaultSorting: &#39;Name ASC&#39;, //Set default sorting
            actions: {
                listAction: &#39;/Demo/StudentList&#39;,
                deleteAction: &#39;/Demo/DeleteStudent&#39;,
                updateAction: &#39;/Demo/UpdateStudent&#39;,
                createAction: &#39;/Demo/CreateStudent&#39;
            },
            fields: {
                StudentId: {
                    key: true,
                    create: false,
                    edit: false,
                    list: false
                },
                Name: {
                    title: &#39;Name&#39;,
                    width: &#39;23%&#39;
                },
                EmailAddress: {
                    title: &#39;Email address&#39;,
                    list: false
                },
                Password: {
                    title: &#39;User Password&#39;,
                    type: &#39;password&#39;,
                    list: false
                },
                Gender: {
                    title: &#39;Gender&#39;,
                    width: &#39;13%&#39;,
                    options: { &#39;M&#39;: &#39;Male&#39;, &#39;F&#39;: &#39;Female&#39; }
                },
                CityId: {
                    title: &#39;City&#39;,
                    width: &#39;12%&#39;,
                    options: &#39;/Demo/GetCityOptions&#39;
                },
                BirthDate: {
                    title: &#39;Birth date&#39;,
                    width: &#39;15%&#39;,
                    type: &#39;date&#39;,
                    displayFormat: &#39;yy-mm-dd&#39;
                },
                Education: {
                    title: &#39;Education&#39;,
                    list: false,
                    type: &#39;radiobutton&#39;,
                    options: { &#39;1&#39;: &#39;Primary school&#39;, 
                               &#39;2&#39;: &#39;High school&#39;, 
                               &#39;3&#39;: &#39;University&#39; }
                },
                About: {
                    title: &#39;About this person&#39;,
                    type: &#39;textarea&#39;,
                    list: false
                },
                IsActive: {
                    title: &#39;Status&#39;,
                    width: &#39;12%&#39;,
                    type: &#39;checkbox&#39;,
                    values: { &#39;false&#39;: &#39;Passive&#39;, &#39;true&#39;: &#39;Active&#39; },
                    defaultValue: &#39;true&#39;
                },
                RecordDate: {
                    title: &#39;Record date&#39;,
                    width: &#39;15%&#39;,
                    type: &#39;date&#39;,
                    displayFormat: &#39;dd.mm.yy&#39;,
                    create: false,
                    edit: false,
                    sorting: false //This column is not sortable!
                }
            }
        });

        //Load student list from server
        $(&#39;#StudentTableContainer&#39;).jtable(&#39;load&#39;);
    });

&lt;/script&gt;</pre>
<h3>
    Server side code</h3>
@Html.Partial("_PagedAndSortedServerSideCodes")

<p>In the server side, we must handle these query string arguments for <strong>paging</strong>:</p>
<ul>
  <li><strong>jtStartIndex</strong>: Start index of records for current page.</li>
  <li><strong>jtPageSize</strong>: Count of maximum expected records.</li>
</ul>
<p>Also, we must handle these query string argument for <strong>sorting</strong>:</p>
<ul>
  <li><strong>jtSorting</strong>:  A string which represents requested sorting. 
  It is built from sorting field name plus sorting direction. 
  For instance, It can be 'Name ASC', 'BirtDate DESC', 'Age ASC'... etc.</li>
</ul>

<p>You can use such an SQL statement for paging and sorting:</p>
<pre class="brush:sql">--MySQL
SELECT * FROM Students ORDER BY Name ASC LIMIT 20,10

--SQL Server
SELECT * FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY Name ASC) AS Row, * FROM Students)
    AS StudentsWithRowNumbers
WHERE Row &gt; 20 AND Row &lt;= 30</pre>
<p> In this SQL statement, we are getting 3rd page (if page size is 10).
We are skipping 20 records (jtStartIndex) and getting 10 records (jtPageSize). Also we used 'Name ASC' for sorting (jtSorting).</p>
<p>Also, one additional information is expected from server:</p>
<ul>
  <li><strong>TotalRecordCount</strong>: Total count of records (not only this page).</li>
</ul>
<p>You can use such an SQL statement to get count of all students:</p>
<pre class="brush:sql">Select Count(*) FROM Students</pre>
